#!bin/bash


if [ -n "$1" ] ; then
  data_date=$1
fi



DIM_MUSIC_ARTIST_ALBUM_INFO_SQL="
WITH
    music AS (
        SELECT
            song_id, song_name, song_alias, artist_id, artist_name, album_id,
            album_name, duration, language, genre, publish_date, copyright_company,
            is_original, is_high_quality, is_free, play_count_total, lyric_count, comment_count, update_time
        FROM wyy_music_warehouse.ods_music_info
    )
    ,artist AS (
        SELECT
            artist_id, artist_name, artist_alias, gender, birth_date,
            country, province, city, category, debut_date, company, fans_count,
            song_count, album_count, mv_count, is_indie, tags, update_time
        FROM wyy_music_warehouse.ods_artist_info
    )
    ,album AS (
        SELECT
            album_id, album_name, artist_id, artist_name, publish_date,
            company, language, genre, song_count, play_count_total, description,
            cover_url, is_digital, price, update_time
        FROM wyy_music_warehouse.ods_album_info
    )
INSERT INTO wyy_music_warehouse.dim_music_artist_album_info PARTITION (dt = '${data_date}')
SELECT
    t1.song_id
    ,t1.song_name
    ,t1.song_alias
    ,t1.artist_id
    ,t2.artist_name
    ,t2.artist_alias
    ,t2.gender AS artist_gender
    ,t2.birth_date AS artist_birth_date
    ,t2.country AS artist_country
    ,t2.province AS artist_province
    ,t2.city AS artist_city
    ,t2.category AS artist_category
    ,t2.debut_date AS artist_debut_date
    ,t2.company AS artist_company
    ,t2.fans_count AS artist_fans_count
    ,t2.song_count AS artist_song_count
    ,t2.album_count AS artist_album_count
    ,t2.mv_count AS artist_mv_count
    ,t1.album_id
    ,t3.album_name
    ,t3.publish_date AS album_publish_date
    ,t3.company AS album_company
    ,t3.language AS album_language
    ,t3.genre AS album_genre
    ,t3.song_count AS album_song_count
    ,t3.play_count_total AS album_play_count_total
    ,t3.price AS album_price
    ,t1.duration AS music_duration
    ,t1.language AS music_language
    ,t1.genre AS music_genre
    ,t1.publish_date AS music_publish_date
    ,t1.copyright_company AS music_copyright_company
    ,t1.play_count_total AS music_play_count_total
    ,t1.lyric_count AS music_lyric_count
    ,t1.comment_count AS music_comment_count
FROM music t1
    LEFT JOIN artist t2 ON t1.artist_id = t2.artist_id
    LEFT JOIN album t3 ON t1.album_id = t3.album_id
;
"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -p 123456 -e "${DIM_MUSIC_ARTIST_ALBUM_INFO_SQL}"